using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Module.Common.Models;
using Module.Core.EntitiesInfo;

namespace Module.Core.DAO
{
	public class SingerDAO
	{
		#region Fields

		protected string connectionStringName;

		#endregion

		
		#region Methods

		/// <summary>
		/// Saves a record to the Singer table.
		/// </summary>
		public virtual int Insert(SingerInfo singerInfo)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@SingerName", singerInfo.SingerName),
				new SqlParameter("@Mobile", singerInfo.Mobile),
				new SqlParameter("@Description", singerInfo.Description),
				new SqlParameter("@CreatedBy", singerInfo.CreatedBy),
				new SqlParameter("@CreatedOn", singerInfo.CreatedOn),
				new SqlParameter("@Address", singerInfo.Address),
				new SqlParameter("@BirthDay", singerInfo.BirthDay),
				new SqlParameter("@Type", singerInfo.Type),
				new SqlParameter("@Images", singerInfo.Images),
				new SqlParameter("@Keyword", singerInfo.Keyword),
				new SqlParameter("@Status", singerInfo.Status)
			};

			return (int) SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, "Singer_Insert", parameters);
		}

		/// <summary>
		/// Updates a record in the Singer table.
		/// </summary>
		public virtual void Update(SingerInfo singerInfo)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@SingerId", singerInfo.SingerId),
				new SqlParameter("@SingerName", singerInfo.SingerName),
				new SqlParameter("@Mobile", singerInfo.Mobile),
				new SqlParameter("@Description", singerInfo.Description),
				new SqlParameter("@CreatedBy", singerInfo.CreatedBy),
				new SqlParameter("@CreatedOn", singerInfo.CreatedOn),
				new SqlParameter("@Address", singerInfo.Address),
				new SqlParameter("@BirthDay", singerInfo.BirthDay),
				new SqlParameter("@Type", singerInfo.Type),
				new SqlParameter("@Images", singerInfo.Images),
				new SqlParameter("@Keyword", singerInfo.Keyword)
				
			};

			SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "Singer_Update", parameters);
		}

		/// <summary>
		/// Deletes a record from the Singer table by its primary key.
		/// </summary>
		public virtual void Delete(int singerId)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@SingerId", singerId)
			};

			SqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, "Singer_Delete", parameters);
		}

		/// <summary>
		/// Selects a single record from the Singer table.
		/// </summary>
		public virtual SingerInfo Select(int singerId)
		{
			SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@SingerId", singerId)
			};

			using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "Singer_Select", parameters))
			{
				if (dataReader.Read())
				{
					return MakeSingerInfo(dataReader);
				}
				else
				{
					return null;
				}
			}
		}

		/// <summary>
		/// Selects all records from the Singer table.
		/// </summary>
        public CHRTList<SingerInfo> SelectAll(int status)
		{
            SqlParameter[] parameters=new SqlParameter[]
                                          {
                                              new SqlParameter("@Status",status)
		};
			using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "Singer_SelectAll",parameters))
			{
				CHRTList<SingerInfo> singerInfoList = new CHRTList<SingerInfo>();
				while (dataReader.Read())
				{
					SingerInfo singerInfo = MakeSingerInfo(dataReader);
					singerInfoList.Add(singerInfo);
				}

				return singerInfoList;
			}
		}
        public CHRTList<SingerInfo> SearchAll(string keyword, int status, int from, int to)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                          {
                                              new SqlParameter("@Keyword",keyword),
                                              new SqlParameter("@Status",status),
                                              new SqlParameter("@From",from),
                                              new SqlParameter("@To",to)
		};
            using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "Singer_SearchAll", parameters))
            {
                CHRTList<SingerInfo> singerInfoList = new CHRTList<SingerInfo>();
                while (dataReader.Read())
                {
                    SingerInfo singerInfo = MakeSingerInfo(dataReader);
                    singerInfoList.Add(singerInfo);
                }

                return singerInfoList;
            }
        }
        public int GetCount(string keyword, int status)
        {
            SqlParameter[] parameters = new SqlParameter[]
                                            {
                                                new SqlParameter("@Keyword", keyword),
                                                new SqlParameter("@Status", status)
                                            };
            return
                (int)SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, "Singer_GetCount", parameters);

        }
		/// <summary>
		/// Creates a new instance of the Singer class and populates it with data from the specified SqlDataReader.
		/// </summary>
		protected virtual SingerInfo MakeSingerInfo(SqlDataReader dataReader)
		{
			SingerInfo singerInfo = new SingerInfo();
			singerInfo.SingerId = SqlClientUtility.GetInt32(dataReader,DbConstants.SINGER.SINGERID, 0);
			singerInfo.SingerName = SqlClientUtility.GetString(dataReader,DbConstants.SINGER.SINGERNAME, String.Empty);
			singerInfo.Mobile = SqlClientUtility.GetString(dataReader,DbConstants.SINGER.MOBILE, String.Empty);
			singerInfo.Description = SqlClientUtility.GetString(dataReader,DbConstants.SINGER.DESCRIPTION, String.Empty);
			singerInfo.CreatedBy = SqlClientUtility.GetString(dataReader,DbConstants.SINGER.CREATEDBY, String.Empty);
			singerInfo.CreatedOn = SqlClientUtility.GetDateTime(dataReader,DbConstants.SINGER.CREATEDON, DateTime.Now);
			singerInfo.Address = SqlClientUtility.GetString(dataReader,DbConstants.SINGER.ADDRESS, String.Empty);
			singerInfo.BirthDay = SqlClientUtility.GetDateTime(dataReader,DbConstants.SINGER.BIRTHDAY, DateTime.Now);
			singerInfo.Type = SqlClientUtility.GetInt32(dataReader,DbConstants.SINGER.TYPE, 0);
			singerInfo.Images = SqlClientUtility.GetString(dataReader,DbConstants.SINGER.IMAGES, String.Empty);
			singerInfo.Keyword = SqlClientUtility.GetString(dataReader,DbConstants.SINGER.KEYWORD, String.Empty);
            singerInfo.Status = SqlClientUtility.GetInt32(dataReader, DbConstants.SINGER.Status, 0);

			return singerInfo;
		}

		#endregion
	}
}
